package com.lm.ledger.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import main.java.org.dss.lm.admin.connection.DatabaseConnection;

import com.lm.ledger.exception.LedgerException;
import com.lm.ledger.utill.Account;
import com.lm.ledger.utill.Location;
import com.lm.ledger.utill.User;

public class LedgerDaoImp implements LedgerDao{

	ResultSet rs;
	PreparedStatement ps;  
	DatabaseConnection lmDB = new DatabaseConnection();
	Connection conn;
	public void saveNewTrx(Account acc){
			String query = "INSERT INTO ACCOUNT ( FormID, LocationID, Amount, Reason, ItemPurchased, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status, TransactionDate, ReturnDate,DateTimerStamp) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		//INSERT INTO ACCOUNT (TransactionID, FormID, LocationID, Amount, Reason, ItemPurchased, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status) VALUES(002, 001, 001, 1000, 'pruchase', 'grain', 111, 1, 'aaa', 'debit', 'cash', 1, 'y') 
		try {		
			
			conn = lmDB.getDBConnection();
			String form = newFormId(conn);
			System.out.println("form form id == "+form);
			ps = conn.prepareStatement(query);
			ps.setString(1, form);
			ps.setString(2, acc.getLocId());
			ps.setDouble(3, acc.getAmount());
			ps.setString(4, acc.getReason());
			ps.setString(5, acc.getItem());
			ps.setDouble(6, acc.getInterestAmount());
			ps.setString(7, acc.getPersonId());
			ps.setString(8, acc.getNarration());
			ps.setString(9, acc.getTxTye());
			ps.setString(10, acc.getMode());
			ps.setString(11, acc.getAuthId());
			ps.setString(12, acc.getStatus());
			ps.setString(13, acc.getTxDate() );
			ps.setString(14, acc.getRetDate());
			ps.setString(15, getCurrentTimeStamp());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();		
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}		
	}
	
	public void updateTrx(Account account){
		System.out.println("updateTrx called");
		String query = "UPDATE ACCOUNT SET Amount=?, Reason=?, ItemPurchased=?, InterestAmount=?, PersonID=?, Narration=?, TransactionType=?, ModeOfPayment=?, TransactionAuthorizerID=?, Status=?,  TransactionDate=?, ReturnDate=?, LocationID=? WHERE FormID=?";
		
		try {
			System.out.println("debug 1");
			conn = lmDB.getDBConnection();
			System.out.println("debug 2");
			ps = conn.prepareStatement(query);
			System.out.println("debug 3");
			ps.setDouble(1, account.getAmount());
			System.out.println("debug 4");
			ps.setString(2, account.getReason());
			System.out.println("debug 5");
			ps.setString(3, account.getItem());
			System.out.println("debug 6");
			ps.setDouble(4, account.getInterestAmount());
			System.out.println("debug 7");
			ps.setString(5, account.getPersonId());
			System.out.println("debug 8");
			ps.setString(6, account.getNarration());
			System.out.println("debug 9");
			ps.setString(7, account.getTxTye());
			System.out.println("debug 10");
			ps.setString(8, account.getMode());
			System.out.println("debug 11");
			ps.setString(9, account.getAuthId());
			System.out.println("debug 12");
			ps.setString(10, account.getStatus());
			System.out.println("debug 13");			
			ps.setString(11, account.getTxDate());
			System.out.println("debug 14");
			ps.setString(12, account.getRetDate());
			System.out.println("debug 15");
			ps.setString(13, account.getLocId());
			System.out.println("debug 16");
			ps.setString(14, account.getFormId());
			System.out.println("debug 17");
			ps.executeUpdate();
			System.out.println("debug 18");
		}  catch (Exception e) {
			System.out.println("exception "+e);
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();			
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}		
		
	}
	

	
	public List searchList1(Account acc){
		List trxList = new ArrayList();
		Account account;
		String formId = acc.getFormId();
		String locId = acc.getLocId();
		Double amount = acc.getAmount();
		String trxDate = acc.getTxDate();
		String trxType = acc.getTxTye();
		String authId = acc.getAuthId();
		String status = acc.getStatus();
		System.out.println("search List......");
		System.out.println();
		String query = null;
		if(formId!=null){
			//query = "SELECT FormID, LocationID, Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status FROM ACCOUNT  where FormID=?";
			query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.FormID=?";
		}else if(locId!=null){
			query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.LocationID=?";
		}else if(trxDate!=null){
			query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.TransactionDate=?";
		}else if(trxType!=null){
			query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.FormID=?";
		}else if(status!=null){
			query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.FormID=?";
		}
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, formId);
			rs = ps.executeQuery();
			 while(rs.next()){
				 account = new Account();				
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));	
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}
		return trxList;
	}
	
	public Account getAccountForFormId(String formId){
		Account account = new Account();
		//String query = "SELECT FormID, LocationID, Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status FROM ACCOUNT  where FormID=?";
		String query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.reject='N' and account.FormID=?";
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, formId);
			rs = ps.executeQuery();
			 while(rs.next()){
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));		 
				 account.setAuthName(rs.getString(17));
			 }		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}
		return account;
	}
	public List getAllTrxs(Double userId){
		String query = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID=location.LocationID and location.GroupId=location_user.GroupId and account.PersonID = user.UserID and account.reject='N' and account.status='N' and account.reject='N' and account.PersonID=? order by DateTimerStamp desc";
	//	String query  ="SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status FROM ACCOUNT, location where account.LocationID = location.LocationID";
	//	String query = "select * from account where status=?";
		List trxList = new ArrayList();
		Account account;
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setDouble(1, userId);
			rs = ps.executeQuery();
			 while(rs.next()){				 
				 account = new Account();				
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}		
		return trxList;
	}
	public void rejectTrx(String formId){
		String query = "UPDATE ACCOUNT SET reject=? WHERE FormID=?";
		String rejectVal = "Y";
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, rejectVal);
			ps.setString(2, formId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List searchApproveTrx(Double userId){
		//String query  ="SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user where account.LocationID = location.LocationID and account.PersonID = user.UserID and account.reject='N' and account.status=? order by DateTimerStamp desc";
		String query  = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID=location.LocationID and location.GroupId=location_user.GroupId and account.PersonID = user.UserID and account.reject='N' and account.status='N' and location_user.UserID=? order by DateTimerStamp desc";
		//String query  ="SELECT FormID, LocationID, Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status FROM ACCOUNT  where status=? ";
		List trxList = new ArrayList();
		Account account;
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setDouble(1, userId);
			rs = ps.executeQuery();
			 while(rs.next()){
				 account = new Account();				
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}		
		return trxList;
	}

	public void approveTrx(Account account){
		System.out.println("approveTrx status ---  "+account.getStatus());
		System.out.println(" approveTrx fomr id ---"+account.getFormId());
		System.out.println(" approveTrx auth id ---"+account.getAuthId());
		String status = account.getStatus();
		System.out.println("status ---- "+status);
		String query = "UPDATE ACCOUNT SET Status=?, TransactionAuthorizerID=? WHERE FormID=?";
		
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, status);
			ps.setString(2, account.getAuthId());
			ps.setString(3, account.getFormId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}	
	}
	
	public List getLocations(Double userId) {
		System.out.println("getLocations(Double userId) ----- "+userId);
		List locations = new ArrayList();
		Location location;
		String query = "SELECT location.LocationID, Name,location_user.UserID FROM location, location_user where location.GroupId =location_user.GroupId and location_user.UserID=?";
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);	
			ps.setDouble(1, userId);
			rs = ps.executeQuery();
			while(rs.next()){
				location = new Location();
				location.setLocId(rs.getString(1));
				location.setLocName(rs.getString(2));
				locations.add(location);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			//throw new LedgerException("Unable to process request.");			
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}
		/*if(locations.isEmpty()){
			throw new LedgerException("There is no location in location list.");
		}*/
		return locations;
	}
	
	public User userDetail(String name){
		System.out.println(" dao --- userDetail --- +"+name);
		User user=null;
		//String query = "SELECT user.UserID, user.Name,user.Password, role.RoleID, role.RoleDescription from user, role where user.RoleID=role.RoleID and user.Name=?";
		String query = "SELECT user.UserID, user.Name,user.Password, role.RoleID, role.RoleDescription, user.InsanID from user, role, user_role where user.UserID=user_role.UserID and user_role.RoleID=role.RoleID and user.InsanID=?";
		try{
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setString(1, name);
			System.out.println("debug ---1");
			rs = ps.executeQuery();
			System.out.println("debug ---2");
			while(rs.next()){
				System.out.println("debug ---3");
				user = new User();
				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(5));
				System.out.println(rs.getString(6));
				user.setUserId(rs.getDouble(1));
				user.setName(rs.getString(2));
				user.setPwd(rs.getString(3));
				user.setRoleId(rs.getString(4));
				user.setRoleName(rs.getString(5));
				user.setInsanNo(rs.getString(6));
			}
		}catch (Exception e) {
			System.out.println("exception --------- "+e);
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}
		System.out.println("name - "+user.getName());
		return user;
	}
	
	public int getFormId(Connection conn){
		
		String query = "SELECT FormId from account where TransactionID = (SELECT MAX(TransactionID) from account)";
		String form = null;
		try{
			
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				form = rs.getString(1);
			}
		}catch (Exception e) {			
		}
		System.out.println("form ==== "+form);
		String val=null;
		if(form!=null){
			System.out.println("inside if");
			val = form.substring(3, form.length());
		}else{
			System.out.println("inside else");
			val = "0";
		}
		System.out.println("value ------------- "+val);
		System.out.println(val);
		int num = Integer.parseInt(val);
		System.out.println("from ---- "+form);
		return num;
	}
	
	private String newFormId(Connection conn){
		int id = getFormId(conn);
		
		id = id+1;
		String form = "ABC"+id;
		
		return form;
	}
	
	private static Account myAcc(){
		Account acc = new Account();
		acc.setFormId("ABC5");
		acc.setLocId("2");
		acc.setReason("Purchased Goods");
		acc.setItem("grain");
		acc.setNarration("qqqqqqqqq");
		acc.setTxTye("Debit");
		acc.setMode("cash");
		acc.setAmount(303.0);
		acc.setInterestAmount(15.0);
		/*3033.0
		2
		ABC5, ABC5
		Purchased Goods
		grain
		qqqqqqqqq
		44.0*/
		return acc;
	}
	private String getCurrentTimeStamp() {
	    SimpleDateFormat sdfDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//dd/MM/yyyy
	    Date now = new Date();
	    String strDate = sdfDate.format(now);
	    return strDate;
	}
	
	private String getUserName(Connection connection,String id){
		String query = "select Name from user where UserID=?";
		String name = null;
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				name = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}
	
	public List searchList(Account acc, Double userId, String from){
		
		List trxList = new ArrayList();
		Account account;
		String formId = acc.getFormId();
		String locId = acc.getLocId();
		String trxDate = acc.getTxDate();
		String trxType = acc.getTxTye();
		//String status = acc.getStatus();
		String status = null;
		System.out.println("searchList form id "+formId);
		System.out.println(" searchList loc id   "+locId);
		System.out.println("searchList trx date   "+trxDate);
		System.out.println("searchList trx type  "+trxType);
		System.out.println("searchList status  "+status);
		if("".equals(formId)){
			formId = null;
		}if("".equals(locId)){
			locId = null;
		}if("".equals(trxDate)){
			trxDate = null;
		}if("".equals(trxType)){
			trxType = null;
		}if("".equals(status)){
			status = null;
		}
		/*String formId = "ABC1";  		//A
		String locId = "1" ;		//B
		String trxDate = "2011-11-24" ;		//C
		String trxType = "Debit" ;		//D
		String status = "true";		//E
*/		System.out.println("search List......");
		String query1 = null;
		if(from.equals("approve")){
			query1 = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID = location.LocationID and location.GroupId=location_user.GroupId and account.PersonID = user.UserID  and account.reject='N' and location_user.UserID=?";
		}if(from.equals("edit")){
			query1 = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID = location.LocationID and location.GroupId=location_user.GroupId and account.PersonID = user.UserID  and account.reject='N' and account.PersonID=?";
		}
		//String query1 = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID = location.LocationID and location.GroupId=location_user.LocationID and account.PersonID = user.UserID  and account.reject='N' and location_user.UserID=?";
		//String query1 = "SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) FROM ACCOUNT, location, user, location_user where account.LocationID = location.LocationID and location.GroupId=location_user.LocationID and account.PersonID = user.UserID  and account.reject='N' and account.UserID=?";
		String query2 = getWhereClouse(formId, locId, trxDate, trxType, status);
		String query3 = " order by DateTimerStamp desc";
		String query = query1 + query2 + query3;
		
		System.out.println("search List...query..."+query);
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setDouble(1, userId);
			if(formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null){
				// where = "and formId=? and locId=? and trxDate=? and trxType=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxDate);
				ps.setString(5, trxType);
				ps.setString(6, status);
			}else if(formId != null && locId !=null && trxDate !=null && trxType !=null){
				// where = "and formId=? and locId=? and trxDate=? and trxType=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxDate);
				ps.setString(5, trxType);
			}else if(formId != null && locId !=null && trxType !=null && status !=null){
				// where = "and formId=? and locId=? and trxType=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxType);
				ps.setString(5, status);
			}else if(formId != null && trxDate !=null && trxType !=null && status !=null){
				// where = "and formId=? and trxDate=? and trxType=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
				ps.setString(5, status);
			}else if(formId != null && locId !=null && trxDate !=null && status !=null){
				// where = "and formId=? and locId=? and trxDate=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxDate);
				ps.setString(5, status);
			}else if(locId !=null && trxDate !=null && trxType !=null && status !=null){
				// where = "and locId=? and trxDate=? and trxType=? and status=?";
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
				ps.setString(5, status);
			}else if(formId != null && locId !=null && trxDate !=null){
				// where = "and formId=? and locId=? and trxDate=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxDate);
			}else if(formId != null && locId !=null && trxType !=null){
				// where = "and formId=? and locId=? and trxType=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, trxType);
			}else if(formId != null && locId !=null && status !=null){
				// where = "and formId=? and locId=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, locId);
				ps.setString(4, status);
			}else if(formId != null && trxDate !=null && status !=null){
				// where = "and formId=? and trxDate=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, trxDate);
				ps.setString(4, status);
			}else if(formId !=null && trxDate !=null && trxType !=null){
				// where = "and formId=? and trxDate=? and trxType=?";
				ps.setString(2, formId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
			}else if(formId != null && trxType !=null && status !=null){
				// where = "and formId=? and trxType=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, trxType);
				ps.setString(4, status);
			}else if(locId !=null && trxDate !=null && trxType !=null){
				// where = "and locId=? and trxDate=? and trxType=?";
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, trxType);
			}else if(locId !=null && trxDate !=null && status !=null){
				// where = "and locId=? and trxDate=? and status=?";
				ps.setString(2, locId);
				ps.setString(3, trxDate);
				ps.setString(4, status);
			}else if(trxDate !=null && trxType !=null && status !=null){
				// where = "and trxDate=? and trxType=? and status=?";
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
				ps.setString(4, status);
			}else if(formId != null && locId !=null){
				// where = "and formId=? and locId=?";
				System.out.println("formId != null && locId !=null");
				ps.setString(2, formId);
				ps.setString(3, locId);
			}else if(trxType !=null && status !=null){
				// where = "and trxType=? and status=?";
				ps.setString(2, trxType);
				ps.setString(3, status);
			}else if(formId != null && trxDate !=null){
				// where = "and formId=? and trxDate=?";
				ps.setString(2, formId);
				ps.setString(3, trxDate);
			}else if(formId != null&& trxType !=null){
				// where = "and formId=? and trxType=?";
				ps.setString(2, formId);
				ps.setString(3, trxType);
			}else if(formId != null && status !=null){
				// where = "and formId=? and status=?";
				ps.setString(2, formId);
				ps.setString(3, status);
			}else if(locId !=null && trxDate !=null){
				// where = "and locId=? and trxDate=?";
				ps.setString(2, locId);
				ps.setString(3, trxDate);
			}else if(locId !=null && trxType !=null){
				// where = "and locId=? and trxType=?";
				ps.setString(2, locId);
				ps.setString(3, trxType);
			}else if(locId !=null && status !=null){
				// where = "and locId=? and status=?";
				ps.setString(2, locId);
				ps.setString(3, status);
			}else if(trxDate !=null && trxType !=null){
				// where = " and trxDate=? and trxType=?";
				ps.setString(2, trxDate);
				ps.setString(3, trxType);
			}else if(trxDate !=null && status !=null){
				// where = "and trxDate=? and status=?";
				ps.setString(2, trxDate);
				ps.setString(3, status);
			}else if(formId != null){
				// where = "and formId=?";
				System.out.println("formId != null");
				ps.setString(2, formId);
			}else if(status != null){
				// where = "and status=?";
				ps.setString(2, status);
			}else if(locId != null){
				// where = "and locId=?";
				ps.setString(2, locId);
			}else if(trxDate != null){
				// where = "and trxDate=?";
				ps.setString(2, trxDate);
			}else if(trxType != null){
				// where = "and trxType=?";
				ps.setString(2, trxType);
			}
						
			rs = ps.executeQuery();
			 while(rs.next()){
				 account = new Account();	
				 System.out.println(rs.getString(1));
				 System.out.println(rs.getString(2));
				 System.out.println(rs.getString(3));
				 System.out.println(rs.getDouble(4));
				 System.out.println(rs.getString(5));
				 System.out.println(rs.getString(6));
				 System.out.println(rs.getString(7));
				 System.out.println(rs.getString(8));
				 System.out.println(rs.getDouble(9));
				 System.out.println(rs.getString(10));
				 System.out.println(rs.getString(11));
				 System.out.println(rs.getString(12));
				 System.out.println(rs.getString(13));
				 System.out.println(rs.getString(14));
				 System.out.println(rs.getString(15));
				 System.out.println(rs.getString(16));
				 System.out.println(rs.getString(17));
				 
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));	
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		}  catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}
		System.out.println("list length....."+trxList.size());
		return trxList;
	}
	private String getWhereClouse(String formId,String locId,String trxDate,String trxType,String status){
		/*
		String formId = "ABC1";  		//A
		String locId = null ;		//B
		String trxDate = null ;		//C
		String trxType = null ;		//D
		String status = null;		//E
		*/
		String where = "";
		if(formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null){
			 System.out.println("formId != null && locId !=null && trxDate !=null && trxType !=null && status !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null && trxDate !=null && trxType !=null){
			 System.out.println("formId != null && locId !=null && trxDate !=null && trxType !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and TransactionType=?";
		}else if(formId != null && locId !=null && trxType !=null && status !=null){
			 System.out.println("formId != null && locId !=null && trxType !=null && status !=null");
			where = "and formId=? and account.LocationID=? and TransactionType=? and account.status=?";
		}else if(formId != null && trxDate !=null && trxType !=null && status !=null){
			 System.out.println("formId != null && trxDate !=null && trxType !=null && status !=null");
			where = "and formId=? and TransactionDate=? and TransactionType=? and account.status=?";
		}else if(formId != null && locId !=null && trxDate !=null && status !=null){
			 System.out.println("formId != null && locId !=null && trxDate !=null && status !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=? and account.status=?";
		}else if(locId !=null && trxDate !=null && trxType !=null && status !=null){
			 System.out.println("locId !=null && trxDate !=null && trxType !=null && status !=null");
			where = "and account.LocationID=? and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null && trxDate !=null){
			 System.out.println("formId != null && locId !=null && trxDate !=null");
			where = "and formId=? and account.LocationID=? and TransactionDate=?";
		}else if(formId != null && locId !=null && trxType !=null){
			 System.out.println("formId != null && locId !=null && trxType !=null");
			where = "and formId=? and account.LocationID=? and TransactionType=?";
		}else if(formId != null && locId !=null && status !=null){
			 System.out.println("formId != null && locId !=null && status !=null");
			where = "and formId=? and account.LocationID=? and account.status=?";
		}else if(formId != null && trxDate !=null && status !=null){
			 System.out.println("formId != null && trxDate !=null && status !=null");
			where = "and formId=? and TransactionDate=? and account.status=?";
		}else if(formId !=null && trxDate !=null && trxType !=null){
			 System.out.println("formId !=null && trxDate !=null && trxType !=null");
			where = "and formId=? and TransactionDate=? and TransactionType=?";
		}else if(formId != null && trxType !=null && status !=null){
			 System.out.println("formId != null && trxType !=null && status !=null");
			where = "and formId=? and TransactionType=? and account.status=?";
		}else if(locId !=null && trxDate !=null && trxType !=null){
			 System.out.println("locId !=null && trxDate !=null && trxType !=null");
			where = "and account.LocationID=? and TransactionDate=? and TransactionType=?";
		}else if(locId !=null && trxDate !=null && status !=null){
			 System.out.println("locId !=null && trxDate !=null && status !=null");
			where = "and account.LocationID=? and TransactionDate=? and account.status=?";
		}else if(trxDate !=null && trxType !=null && status !=null){
			 System.out.println("trxDate !=null && trxType !=null && status !=null");
			where = "and TransactionDate=? and TransactionType=? and account.status=?";
		}
		else if(formId != null && locId !=null){
			 System.out.println("formId != null && locId !=null");
			where = "and formId=? and account.LocationID=?";
		}else if(trxType !=null && status !=null){
			 System.out.println("trxType !=null && status !=null");
			where = "and TransactionType=? and account.status=?";
		}else if(formId != null && trxDate !=null){
			 System.out.println("formId != null && trxDate !=null");
			where = "and formId=? and TransactionDate=?";
		}else if(formId != null && trxType !=null){
			System.out.println("formId != null&& trxType !=null");
			where = "and formId=? and TransactionType=?";
		}else if(formId != null && status !=null){
			 System.out.println("formId != null && status !=null");
			where = "and formId=? and account.status=?";
		}else if(locId !=null && trxDate !=null){
			 System.out.println("locId !=null && trxDate !=null");
			where = "and account.LocationID=? and TransactionDate=?";
		}else if(locId !=null && trxType !=null){
			 System.out.println("locId !=null && trxType !=null");
			where = "and account.LocationID=? and TransactionType=?";
		}else if(locId !=null && status !=null){
			 System.out.println("locId !=null && status !=null");
			where = "and account.LocationID=? and account.status=?";
		}else if(trxDate !=null && trxType !=null){
			 System.out.println("and TransactionDate=? and TransactionType=?");
			where = " and TransactionDate=? and TransactionType=?";
		}else if(trxDate !=null && status !=null){
			 System.out.println("and TransactionDate=? and account.status=?");
			where = "and TransactionDate=? and account.status=?";
		}
		else if(formId != null){
			 System.out.println("formId != null");
			where = "and formId=?";
		}else if(status != null){
			 System.out.println("status != null");
			where = "and account.status=?";
		}else if(locId != null){
			 System.out.println("locId != null");
			where = "and account.LocationID=?";
		}else if(trxDate != null){
			 System.out.println("trxDate != null");
			where = "and TransactionDate=?";
		}else if(trxType != null){
			 System.out.println("trxType != null");
			where = "and TransactionType=?";
		}
		
		return where;
	}

	public List searchAllUserTrx(Double userId){
		String query  ="SELECT FormID, account.LocationID, location.name,  Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, account.Status, user.Name, (select Name from user where UserID=account.TransactionAuthorizerID ) from location, location_user, user, account where account.LocationID = location.LocationID and location.GroupId=location_user.GroupId and account.PersonID = user.UserID and account.status='N' and account.reject='N' and account.PersonID=? order by DateTimerStamp desc";
		//Stsring query  ="SELECT FormID, LocationID, Amount, Reason, ItemPurchased, TransactionDate, ReturnDate, InterestAmount, PersonID, Narration, TransactionType, ModeOfPayment, TransactionAuthorizerID, Status FROM ACCOUNT  where status=? ";
		List trxList = new ArrayList();
		Double d = 1.0;
		Account account;
		try {
			conn = lmDB.getDBConnection();
			ps = conn.prepareStatement(query);
			ps.setDouble(1, userId);
			rs = ps.executeQuery();
			 while(rs.next()){
				 account = new Account();				
				 account.setFormId(rs.getString(1));
				 account.setLocId(rs.getString(2));
				 account.setLocName(rs.getString(3));
				 account.setAmount(rs.getDouble(4));
				 account.setReason(rs.getString(5));
				 account.setItem(rs.getString(6));
				 account.setTxDate(rs.getString(7));
				 account.setRetDate(rs.getString(8));
				 account.setInterestAmount(rs.getDouble(9));
				 account.setPersonId(rs.getString(10));
				 account.setNarration(rs.getString(11));
				 account.setTxTye(rs.getString(12));
				 account.setMode(rs.getString(13));
				 account.setAuthId(rs.getString(14));
				 account.setStatus(rs.getString(15));	
				 account.setPersonName(rs.getString(16));
				 account.setAuthName(rs.getString(17));
				 trxList.add(account);
			 }		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();				
				if(conn!=null)
					conn.close();
				}
			catch(SQLException ee){				
			}
		}		
		return trxList;
	}

	public static void main(String[] args) {
		LedgerDaoImp dao = new LedgerDaoImp();
		//List list = dao.searchAllUserTrx("1");
		//System.out.println("size = "+list.size());
	}
}
